"""
Case Type   : 事务-ustore
Case Name   : 索引->事务->子事务->连续Update数据,一次变长，一次变短，总长度不变->子回滚->主回滚->重启数据库
Create At   : 2022/1/14
Owner       : @kyleze
Description :
    1.创建表
    2.在事务中，连续Update数据,一次变长，一次变短，总长度不变
    3.重启数据库
    4.查询数据
    5.环境清理
Expect      :
    1.成功
    2.成功
    3.成功
    4.查询有数据
    5.成功
History     :
"""
import os
import sys
import unittest

from yat.test import macro
from yat.test import Node

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class USTORE(unittest.TestCase):
    def setUp(self):
        self.pri_user = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.schema = 'schema_ustore_trans'
        self.table = 't_ustore_trans_0094'
        text = f'---{os.path.basename(__file__)} start---'
        self.log.info(text)
    
    def testunit_1(self):
        text = '----step1:创建表;expect:成功----'
        self.log.info(text)
        q_str = f'''drop table if exists {self.schema}.{self.table} cascade;
            create table {self.schema}.{self.table}(c_id int,c_d_id int not null,
            c_w_id int not null,c_first varchar(16) not null,c_middle char(22),
            c_last varchar(16) not null,c_street_1 varchar(20) not null,c_street_2 varchar(20),c_city varchar(20)
            not null,
            c_state char(22) not null,c_zip char(9) not null,c_phone char(16) not null,
            c_since timestamp,c_credit char(22) not null,c_credit_lim numeric(22,12),c_discount
            numeric(4,4),c_balance numeric(22,12),c_ytd_payment real not null,c_payment_cnt
            number not null,c_delivery_cnt bool not null,c_end date not null,c_data clob,c_clob clob)
            with (storage_type=ustore);
            create unique index {self.schema}.index_ustore_trans_0094_1 on
            {self.schema}.{self.table}(c_id,c_d_id);
            create index {self.schema}.index_ustore_trans_0094_02 on 
            {self.schema}.{self.table}(c_id);
            create index {self.schema}.index_ustore_trans_0094_03 on 
            {self.schema}.{self.table}(c_city);
            create index {self.schema}.index_ustore_trans_0094_04 on 
            {self.schema}.{self.table}(c_first,c_state);
            create index {self.schema}.index_ustore_trans_0094_05 on 
            {self.schema}.{self.table}(c_id,c_d_id,c_middle);
            create index {self.schema}.index_ustore_trans_0094_06 on 
            {self.schema}.{self.table}(c_id,c_d_id,c_middle,c_street_1);
            insert into {self.schema}.{self.table} 
            select * from {self.schema}.t_ustore_trans_0001;
            '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, q_res,
                      '执行失败: ' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, q_res,
                      '执行失败: ' + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, q_res,
                      '执行失败: ' + text)

        text = '----step2:在事务中，连续Update数据,一次变长，一次变短，总长度不变;expect:成功----'
        self.log.info(text)
        q_str = f'''start transaction;
            update {self.schema}.{self.table} set c_first=c_first||'aa',
            c_data='qvldetanrbrburbmzqujlbacpjqdhjchvgbnrkjrgjrycsgppsocnevautzfeosviaxbvobffnjuq
            hlvnwuqhtgjqsbfacwjpbvpgthpyxcpmnutcjxrbxxbmrmwwxcepwiixvvleyajautcesljhrsfsmsnmzjcxv
            cuxdwmyijbwywiirsgocwktedbbokhynznceaesuifkgoaafagugetfhbcylksrjukvbufqcvbffaxnzssyqui
            dvwefktknrchyxfphunqktwnipnsrvqswsymocnoexbabwnpmnxsvshdsjhazcauvqjgvqjfkjjgqrceyjmbumk
            apmcbxeashybpgekjkfezthnjbhfqiwbutbxtkjkndyyhjchvgbnrkjrgjrycsgppsocnevautzfeosviaxbvobf',
            c_clob='qvldetanrbrburbmzqujsocnevautzfeosviaxbvobffnjuqhlvnwuqhtgjqsbfacwjpbvpgthdaffda
            fdafdafdasfdbmrmwwxcepwiixvvleyajautcesljhrsfsmsnmzjcxvcuxdwmyijbwywiirsgocwktedbbokhynz
            nceaesuifkgoaafagugetfhbcylksrjukvbufqcvbffaxnzssyquidvwefktknrchyxfphunqktwnipnsrvqswsy
            mocnoexbabwnpmnxsvshdsjhazcauvqjgvqjfkjjgqrceyjmbumkapmcbxeashybpgekjkfezthnjbhfqiwbutbxt
            kjkndyyhjchvgbnrkjrgjrycsgppsocnevautzfsdfeosviax' where mod(c_id,3)=0;
            savepoint point0094;
            update {self.schema}.{self.table} set c_first=c_first||'aa',
            c_data=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',350,
            'yxcfgdsgtcsdsjxrbxxbm'),
            c_clob=lpad('sbfacwjpbvpgthpyxcpmnutcjdfaxrbxxbm',500,
            'yxcpmnutcjxrbxxbm') where mod(c_id,22)=1;
            select distinct c_first,count(*) from {self.schema}.{self.table} 
            group by c_first order by c_first;
            rollback to savepoint point0094;
            select distinct c_first,count(*) from {self.schema}.{self.table} 
            group by c_first order by c_first;
            update {self.schema}.{self.table} set c_first=c_first||'aa',
            c_data=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',350,
            'yxcfgdsgtcsdsjxrbxxbm'),
            c_clob=lpad('sbfacwjpbvpgthpyxcpmnutcjdfaxrbxxbm',500,
            'yxcpmnutcjxrbxxbm') where mod(c_id,22)=1;
            select distinct c_first,count(*) from {self.schema}.{self.table} 
            group by c_first order by c_first;
            rollback;
            select distinct c_first,count(*) from {self.schema}.{self.table} 
            group by c_first order by c_first;
        '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('START TRANSACTION', q_res, '执行失败: ' + text)
        self.assertIn('SAVEPOINT', q_res, '执行失败: ' + text)
        self.assertIn('ROLLBACK', q_res, '执行失败: ' + text)
        self.assertIn('1001 rows', q_res, '执行失败: ' + text)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, q_res,
                      '执行失败: ' + text)
        
        text = '----step3:重启数据库;expect:成功----'
        self.log.info(text)
        result = self.pri_sh.restart_db_cluster()
        self.log.info(result)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue('Degraded' in status or 'Normal' in status)

        text = '----step4:查询数据;expect:1001----'
        self.log.info(text)
        q_str = f'select distinct c_first,count(*) ' \
                 f'from {self.schema}.{self.table} ' \
                  f'group by c_first order by c_first;'
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('1001 rows', q_res, '执行失败: ' + text)

    def tearDown(self):
        text = '----step5:清理环境;expect:成功----'
        self.log.info(text)
        sql = f'drop table {self.schema}.{self.table} cascade;'
        result = self.pri_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      '执行失败: ' + text)
        text = f'---{os.path.basename(__file__)} end---'
        self.log.info(text)
